# !/usr/bin/env python
# -*- coding:utf-8 -*-
from modelDb.baseModel import baseModel
import pymysql

class touristsActionsModel(baseModel):
    def __init__(self):
        super(touristsActionsModel, self).__init__()
        self._table = "tourists_actions"

    def _getTable(self, phone):
        tableName = "tourists_actions_%d" % (int(phone) % 100)
        return tableName


    def addMany(self, datas):
        """
        添加多条客源行为
        :param data:
        :return:
        """
        cursor = self.conn.cursor()
        for data in datas:
            data.setdefault("tableName", self._getTable(data.get("phone")))
            sql = """
                insert into %(tableName)s(
                     id , phone,user_id , city , data_status , imei ,appName, platform_type,
                     idfa , user_behavior , search_term , page_type ,purpose_desc,
                     visit_time , house_province , house_city , cityarea_id ,cityarea_name,cityarea_name_source,
                     cityarea2_id , cityarea2_name, cityarea2_name_source,house_name , unit_price , house_price ,
                     house_room , house_hall , house_toilet , borough_name ,borough_name_source,
                     borough_id , house_totalarea , house_toward ,
                     house_topfloor , house_floor , house_fitment ,
                     building_type , house_type , property_right , house_address
                ) values(
                    '%(id)s', '%(phone)s','%(user_id)s', '%(city)s', '%(data_status)s', '%(imei)s','%(appName)s','%(platform_type)s',
                    '%(idfa)s', '%(user_behavior)s', '%(search_term)s', '%(page_type)s','%(purpose_desc)s',
                    '%(visit_time)s', '%(house_province)s', '%(house_city)s', '%(cityarea_id)s','%(cityarea_name)s','%(cityarea_name_source)s',
                    '%(cityarea2_id)s','%(cityarea2_name)s', '%(cityarea2_name_source)s', '%(house_name)s', '%(unit_price)s', '%(house_price)s',
                    '%(house_room)s', '%(house_hall)s', '%(house_toilet)s', '%(borough_name)s','%(borough_name_source)s',
                    '%(borough_id)s', '%(house_totalarea)s', '%(house_toward)s',
                    '%(house_topfloor)s', '%(house_floor)s', '%(house_fitment)s',
                    '%(building_type)s', '%(house_type)s', '%(property_right)s', '%(house_address)s'
                )
                """ % data
            print sql
            cursor.execute(sql)
            self.conn.commit()

        self.conn.commit()
        cursor.close()
        self.conn.close()
        return True


    # def findByPhone(self, phone):
    #     """
    #     通过手机查找客源数据
    #     :param phone:
    #     :return:
    #     """
    #     cursor = self.conn.cursor()
    #
    #     sql = "select * from %(table)s where phone = '%(phone)s'" % {"table":self._table, "phone":phone}
    #     cursor.execute(sql)
    #     result = cursor.fetchone()
    #
    #     #self.conn.commit()
    #     cursor.close()
    #     return result


    def selectEtl(self, num):
        """
        获取etl清洗的数据
        :return:
        """
        tableName =  self._getTable(num)

        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql = "select * from %(table)s where data_status = 1 and is_del=1 limit 1000" % {"table":tableName}
        cursor.execute(sql)
        result = cursor.fetchall()

        #self.conn.commit()
        cursor.close()
        return result


    #====================================================
    # 跟新etl清洗的数据
    #====================================================
    def updateEtl(self, data, data_status):
        tableName = self._getTable(data.get('phone'))
        cursor = self.conn.cursor()
        id = data.get("id")

        sql = "update %s set data_status='%d' where id='%s'" % (tableName, data_status, id)
        effect_row = cursor.execute(sql)
        self.conn.commit()

        # 关闭游标
        cursor.close()
        return effect_row

    #====================================================
    # 跟新etl清洗的数据
    #====================================================
    def updateCheckToEtl(self, tourists_id, data_status, phone):
        tableName = self._getTable(phone)

        cursor = self.conn.cursor()

        sql = "update %s set data_status=%d where user_id=%d and data_status=4" % (tableName, data_status, tourists_id)
        effect_row = cursor.execute(sql)
        self.conn.commit()

        # 关闭游标
        cursor.close()
        return effect_row


    #====================================================
    # 获取画像的数据
    #====================================================
    def selectPortrait(self, num):
        tableName = self._getTable(num)

        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql = "select *  from %s where data_status = 2 and is_del=1 limit 1000" %(tableName)
        cursor.execute(sql)
        result = cursor.fetchall()

        cursor.close()
        return result